﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Globalization;
using System.Text;

namespace DGen
{
    /// <summary>
    /// generic db layer for sql server connections
    /// 16th May 2009
    /// </summary>
    public class db
    {
        const string defaultApplicationName = "db class (v1.1)";

        // FIELDS
        private SqlConnection theConnection;
        private string serverName;
        private bool integratedSecurity;
        private string userName;
        private string userPassword;
        private string databaseName;
        private string appName;

        internal int rowCount;
        internal int queryCount;
        internal int lastQueryTimer;

        internal StringBuilder errorLog;



        // CONSTRUCTORS
        /// <summary>
        /// Supply server name, database name and valid SQL username and password.
        /// </summary>
        /// <param name="server"></param>
        /// <param name="dbName"></param>
        /// <param name="UID"></param>
        /// <param name="PWD"></param>
        public db(string server, string dbName, string UID, string PWD)
        {
            if (UID.Trim().Length < 2)
            {
                UID = "sa";
            }
            if (PWD.Trim().Length < 2)
            {
                PWD = "someStupidPasswordWhichCantPossiblyBeTheRealOneCanItIMeanAsideFromAnythingElseWhatsWithTheBizarreCapitalisationAndStuffButSeriouslyNoThisIsntARealPasswordOrIsIt";
            }
            userName = UID;
            userPassword = PWD;
            integratedSecurity = false;

            initDB(server, dbName);
        }
        /// <summary>
        /// Constructor for integrated security use.
        /// </summary>
        /// <param name="server"></param>
        /// <param name="dbName"></param>
        /// <param name="useIntegratedSecurity"></param>
        public db(string server, string dbName, bool useIntegratedSecurity)
        {
            if (useIntegratedSecurity == false)
            {
                InvalidOperationException myException = new InvalidOperationException("Invalid use of integrated security - must be set to TRUE");
                throw myException;
            }
            integratedSecurity = true;
            initDB(server, dbName);
        }
        private void initDB (string server, string dbName)
        {


            if (server.Trim ().Length < 2)
            {
                server = "127.0.0.1";
            }
            if (dbName.Trim ().Length < 2)
            {
                dbName = "master";
            }
            serverName = server;
            databaseName = dbName;
            appName = defaultApplicationName;
            resetCounts ();
            try
            {
                theConnection = new SqlConnection (ConnectionString ());
                theConnection.Open ();

                writeToLog ("Connection All OK");
                theConnection.Close ();

            }
            catch (Exception ex)
            {
                writeToLog(ex.Message);
            }

        }



        // END OF CONSTRUCTORS

        /// <summary>
        /// Dynamic SQL : Takes query, returns DataTable. Only use where resultset is expected/needed.
        /// </summary>
        /// <param name="SqlString"></param>
        /// <returns></returns>
        public DataTable RenderSQL(string SqlString)
        {
            DataTable dt = new DataTable();
            dt.Locale = CultureInfo.InvariantCulture;
            try
            {
                using (SqlConnection conn = new SqlConnection(ConnectionString()))
                {
                    using (SqlCommand cmd = new SqlCommand(SqlString, conn))
                    {
                        cmd.CommandType = CommandType.Text;
                        conn.Open();
                        IDataReader dr = cmd.ExecuteReader();
                        dt.Load(dr);
                        rowCount = dt.Rows.Count;
                        conn.Close();
                        return dt;
                    }
                }
            }
            catch (Exception ex)
            { writeToLog("ERROR:" + ex.Message); }

            return dt;
        }

        /// <summary>
        /// Takes StoredProcedure name and parameter array and returns DataTable with Results.
        /// </summary>
        /// <param name="SqlString"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public DataTable RenderSQL(string SqlString, SqlParameter[] param)
        {
            DataTable dt = new DataTable();
            dt.Locale = CultureInfo.InvariantCulture;
            try
            {
                using (SqlConnection conn = new SqlConnection(ConnectionString()))
                {
                    using (SqlCommand cmd = new SqlCommand(SqlString, conn))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        if (param.Length > 0)
                        {
                            foreach (SqlParameter p in param)
                            {
                                cmd.Parameters.Add(p);
                            }
                        }
                        conn.Open();
                        IDataReader dr = cmd.ExecuteReader();
                        dt.Load(dr);
                        rowCount = dt.Rows.Count;
                        writeToLog("Query processed OK. " + rowCount.ToString() + " row(s) affected");
                        conn.Close();
                        return dt;
                    }
                }
            }
            catch (Exception ex)
            { writeToLog("ERROR:" + ex.Message); }

            return dt;
        }
        /// <summary>
        /// Override:forces DB to treat query as procedure despite no params being supplied.
        /// </summary>
        /// <param name="SqlString"></param>
        /// <param name="isProcedure"></param>
        /// <returns></returns>
        public DataTable RenderSQL(string SqlString, bool isProcedure)
        {
            SqlParameter[] fakeParameters = new SqlParameter[0];
            return RenderSQL(SqlString, fakeParameters);

        }
        /// <summary>
        /// override:Single parameter object passed.
        /// </summary>
        /// <param name="SqlString"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public DataTable RenderSQL(string SqlString, SqlParameter param)
        {
            SqlParameter[] fakeParameters = new SqlParameter[1];
            fakeParameters[0] = param;
            return RenderSQL(SqlString, fakeParameters);

        }
        /// <summary>
        /// Override:for single param queries where param is INT only.
        /// </summary>
        /// <param name="SqlString"></param>
        /// <param name="singleParameterName"></param>
        /// <param name="singleParameterValue"></param>
        /// <returns></returns>
        public DataTable RenderSQL(string SqlString, string singleParameterName, int singleParameterValue)
        {
            SqlParameter[] fakeParameters = new SqlParameter[1];
            fakeParameters[0] = new SqlParameter(singleParameterName, singleParameterValue);
            return RenderSQL(SqlString, fakeParameters);
        }
        /// <summary>
        /// Override:for single param queries where param is STRING only.
        /// </summary>
        /// <param name="SqlString"></param>
        /// <param name="singleParameterName"></param>
        /// <param name="singleParameterValue"></param>
        /// <returns></returns>
        public DataTable RenderSQL(string SqlString, string singleParameterName, string singleParameterValue)
        {
            SqlParameter[] fakeParameters = new SqlParameter[1];
            fakeParameters[0] = new SqlParameter(singleParameterName, singleParameterValue);
            return RenderSQL(SqlString, fakeParameters);
        }

        private string dataTable2Text(DataTable theDT)
        {
            StringBuilder sb = new StringBuilder(theDT.Rows.Count * 10);
            foreach (DataColumn dc in theDT.Columns)
            {
                sb.Append(dc.ColumnName);
                sb.Append("\t");
            }
            sb.AppendLine();
            foreach (DataRow dr in theDT.Rows)
            {
                foreach (object columnObj in dr.ItemArray)
                {
                    string columnValue = columnObj.ToString();
                    sb.Append(columnValue);
                    sb.Append("\t");
                }
                sb.AppendLine();
            }
            return sb.ToString();
        }

        /// <summary>
        /// returns column of integers - first column only. Input has to be a stored procedure.
        /// </summary>
        /// <param name="procName"></param>
        /// <returns></returns>
        public int[] GetColumn(string procName)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("ID", typeof(int));
            string SqlString = procName;
            using (SqlConnection conn = new SqlConnection(ConnectionString()))
            {
                using (SqlCommand cmd = new SqlCommand(SqlString, conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    conn.Open();
                    //IDataReader dr = cmd.ExecuteReader();
                    IDataReader dr = cmd.ExecuteReader();
                    dt.Load(dr);
                }


            }
            int rowCount = dt.Rows.Count;
            int[] myArray = new int[rowCount];
            for (int i = 0; i < rowCount; i++)
            {
                try
                {
                    object myObject = dt.Rows[i][1];
                    int x = Convert.ToInt32(myObject);
                    myArray[i] = x;
                }
                catch
                {
                    myArray[i] = 3;
                }
            }
            return myArray;


        }



        private void writeToLog(string snippet)
        {
            errorLog.Append(DateTime.Now.TimeOfDay + "\t\t");
            errorLog.Append(snippet);
            errorLog.AppendLine();
        }

        private void resetCounts()
        {
            rowCount = 0;
            queryCount = 0;
            lastQueryTimer = 0;
            errorLog = new StringBuilder();
            writeToLog("Class initialised");
        }
        /// <summary>
        /// Connection string property as it currently stands.
        /// </summary>
        /// <returns></returns>
        public string ConnectionString()
        {
            string connectionString;
            connectionString = "Data Source=" + serverName + ";";
            connectionString += "Initial Catalog=" + databaseName + ";";
            connectionString += ";Application Name=" + appName + ";";
            if (integratedSecurity == true)
            { connectionString += "Integrated Security=True;"; }
            if (integratedSecurity == false)
            {
                connectionString += "User Name=" + userName + ";";
                connectionString += "Password=" + userPassword + ";";
            }
            return connectionString;
        }
        /// <summary>
        /// Overriden:outputs diagnostic messages as string.
        /// </summary>
        /// <returns></returns>
        public override string ToString()
        {
            return errorLog.ToString();
        }
        /// <summary>
        /// executes Sql statement, returns a row count. For updates, deletes, etc. 
        /// </summary>
        /// <param name="cmdX"></param>
        /// <returns></returns>
        public int ExecuteSql(SqlCommand cmdX)
        {
            using (var conn = new SqlConnection(ConnectionString()))
            {
                conn.Open();
                cmdX.Connection = conn;
                try
                {
                    rowCount = cmdX.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    errorLog.AppendLine(ex.Message.ToString());
                    rowCount = -1;
                }

                return Convert.ToInt32(rowCount);
            }
        }

        /// <summary>
        /// executes sql, gives rowcount.
        /// </summary>
        /// <param name="SqlString"></param>
        /// <returns></returns>
        public int ExecuteSql(string SqlString)
        {
 

            var cmd = new SqlCommand();
            cmd.CommandText = SqlString;

            rowCount = Convert.ToInt32(ExecuteSql(cmd));
       
            return rowCount;
        }
    }
}